/*DONE* Table [dbo].[<CompanyName>$Retail Setup$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 29.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Retail Setup$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Retail Setup$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Key] [nvarchar](10) NOT NULL,
	[Local Store No_] [nvarchar](10) NOT NULL,
	[LS Retail in Use] [tinyint] NOT NULL,
	[EAN License No_] [nvarchar](10) NOT NULL,
	[Def_ VAT Bus_ Post Gr_ (Price)] [nvarchar](10) NOT NULL,
	[Def_ Price Includes VAT] [tinyint] NOT NULL,
	[Default Logo Image] [uniqueidentifier] NOT NULL,
	[LS Retail Logo Image] [uniqueidentifier] NOT NULL,
	[Source Code] [nvarchar](10) NOT NULL,
	[Store No_ Nos_] [nvarchar](10) NOT NULL,
	[Item Sales Statistics on] [int] NOT NULL,
	[POS Terminal Statistics] [tinyint] NOT NULL,
	[Staff Statistics] [tinyint] NOT NULL,
	[Payment Statistics] [tinyint] NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Days Before Trans_ Archive] [int] NOT NULL,
	[Commission Active] [tinyint] NOT NULL,
	[Calculate in Statem_Posting] [tinyint] NOT NULL,
	[Calculate in Sales Posting] [tinyint] NOT NULL,
	[Exclude Returns] [tinyint] NOT NULL,
	[Bal_ Acc_ Type] [int] NOT NULL,
	[Bal_ Acc_ No_] [nvarchar](20) NOT NULL,
	[Original Salespers_in Returns] [tinyint] NOT NULL,
	[Dimension 1 Mandatory] [tinyint] NOT NULL,
	[Only Two Dimensions] [tinyint] NOT NULL,
	[Default Price Group] [nvarchar](10) NOT NULL,
	[Post Total Disc_] [tinyint] NOT NULL,
	[Post Infocode Disc_] [tinyint] NOT NULL,
	[Post Line Disc_] [tinyint] NOT NULL,
	[Post Periodic Disc_] [tinyint] NOT NULL,
	[Post Cust_ Disc_] [tinyint] NOT NULL,
	[Post Coupon Disc_] [tinyint] NOT NULL,
	[Post Line Disc_ Offer] [tinyint] NOT NULL,
	[Post Total Disc_ Offer] [tinyint] NOT NULL,
	[Post Tender Type Disc_] [tinyint] NOT NULL,
	[Item Posting Date] [int] NOT NULL,
	[Default Customer Posting] [int] NOT NULL,
	[Update Cost Amount] [tinyint] NOT NULL,
	[Item Labels for Neg_ Stock] [tinyint] NOT NULL,
	[Item Labels on Price Change] [tinyint] NOT NULL,
	[Post Always Reserve Items] [tinyint] NOT NULL,
	[Delete Printed  Labels] [tinyint] NOT NULL,
	[Days to keep Label History] [int] NOT NULL,
	[PO Item Lookup Method] [int] NOT NULL,
	[Sale Compare Active] [tinyint] NOT NULL,
	[Sale Compare Date Value] [varchar](32) NOT NULL,
	[Create Items No_ Series] [nvarchar](10) NOT NULL,
	[Default Item Hierarchy] [nvarchar](20) NOT NULL,
	[Distribution Location] [nvarchar](10) NOT NULL,
	[Def_ Store Hierarchy] [nvarchar](20) NOT NULL,
	[IniFile Section Identifier] [int] NOT NULL,
	[IniFile Path] [nvarchar](100) NOT NULL,
	[Barcode Lookup in Sales Line] [int] NOT NULL,
	[Default Subgroup Name] [nvarchar](10) NOT NULL,
	[Allow Rename] [tinyint] NOT NULL,
	[LS Retail Logo] [image] NULL,
	[System In DEMO MODE] [tinyint] NOT NULL,
	[Preaction Creation by] [int] NOT NULL,
	[Message No_ Series] [nvarchar](10) NOT NULL,
	[PO Version No_ Markings] [int] NOT NULL,
	[Register Card Information] [tinyint] NOT NULL,
	[Line Disc_ Manual Sequence] [int] NOT NULL,
	[Line Disc_ Manual Function] [int] NOT NULL,
	[Total Disc_ Manual Sequence] [int] NOT NULL,
	[Total Disc_ Manual Function] [int] NOT NULL,
	[Infocode Discount Sequence] [int] NOT NULL,
	[Infocode Discount Function] [int] NOT NULL,
	[Coupon Discount Sequence] [int] NOT NULL,
	[Coupon Discount Function] [int] NOT NULL,
	[Item Point Offer Sequence] [int] NOT NULL,
	[Item Point Offer Function] [int] NOT NULL,
	[Total Discount Offer Sequence] [int] NOT NULL,
	[Total Discount Offer Function] [int] NOT NULL,
	[Tender Type Discount Function] [int] NOT NULL,
	[Autom_ Statistics Update] [tinyint] NOT NULL,
	[Include Special Groups] [tinyint] NOT NULL,
	[ABC Last Run Sales From] [datetime] NOT NULL,
	[ABC Last Run Sales To] [datetime] NOT NULL,
	[ABC Date Formula] [varchar](32) NOT NULL,
	[ABC Sales A] [int] NOT NULL,
	[ABC Sales B] [int] NOT NULL,
	[ABC Sales C] [int] NOT NULL,
	[ABC Profit A] [int] NOT NULL,
	[ABC Profit B] [int] NOT NULL,
	[ABC Profit C] [int] NOT NULL,
	[ABC Last Run Profit From] [datetime] NOT NULL,
	[ABC Last Run Profit To] [datetime] NOT NULL,
	[Validation period Nos_] [nvarchar](10) NOT NULL,
	[Min_ Password Length] [int] NOT NULL,
	[Use JavaScript Add-Ins Only] [tinyint] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[W1 for Country  Code] [int] NOT NULL,
 CONSTRAINT ['+@Company+'$Retail Setup$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Retail Setup$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC(@CreateTable)

END